﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Security.Cryptography;
using Entity;

namespace DAO
{
    public class NhanVienDAO
    {
        private static string MD5(string password)
        {
            byte[] textBytes = System.Text.Encoding.Default.GetBytes(password);
            try
            {
                MD5CryptoServiceProvider cryptHandler;
                cryptHandler = new MD5CryptoServiceProvider();
                byte[] hash = cryptHandler.ComputeHash(textBytes);
                string ret = "";
                foreach (byte a in hash)
                {
                    if (a < 16)
                        ret += "0" + a.ToString("x");
                    else
                        ret += a.ToString("x");
                }
                return ret;
            }
            catch
            {
                throw;
            }
        }

        public static Nhan_Vien DangNhap(String CMND, String MatKhau)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                { 
                    connect = Connection.GetConnection(); 
                }
                catch
                { 
                    throw new Exception("The connection with the database can´t be established"); 
                }

                SqlParameter[] par = new SqlParameter[3];
                par[0] = new SqlParameter("@CMND", CMND);
                par[1] = new SqlParameter("@MatKhau", MD5(MatKhau));
                par[2] = new SqlParameter("@KhongHoatDong", false);

                String cmdText = "SELECT * FROM NHAN_VIEN WHERE CMND = @CMND AND MatKhau = @MatKhau AND KhongHoatDong = @KhongHoatDong";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, par).Tables[0];
            }
            catch (Exception ex)
            { 
                throw new Exception(ex.Message); 
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            if (rs.Rows.Count == 0)
                return null;
            return new Nhan_Vien(rs.Rows[0]["TenNV"].ToString(), rs.Rows[0]["CMND"].ToString(), 
                rs.Rows[0]["SoDienThoai"].ToString(), rs.Rows[0]["DiaChi"].ToString(), 
                rs.Rows[0]["Email"].ToString(), rs.Rows[0]["MatKhau"].ToString(), 
                Boolean.Parse(rs.Rows[0]["QuanLy"].ToString()), Boolean.Parse(rs.Rows[0]["KhongHoatDong"].ToString()),
                DateTime.Parse(rs.Rows[0]["NgayVaoLam"].ToString()));
        }

        public static void ThemNhanVien(Nhan_Vien nv)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[9];
                par[0] = new SqlParameter("@CMND", nv.CMND);
                par[1] = new SqlParameter("@DiaChi", nv.DiaChi);
                par[2] = new SqlParameter("@DienThoai", nv.DienThoai);
                par[3] = new SqlParameter("@Email", nv.Email);
                par[4] = new SqlParameter("@KhongHoatDong", nv.KhongHoatDong);
                par[5] = new SqlParameter("@MatKhau", MD5(nv.MatKhau));
                par[6] = new SqlParameter("@QuanLy", nv.QuanLy);
                par[7] = new SqlParameter("@TenNhanVien", nv.TenNhanVien);
                par[8] = new SqlParameter("@NgayVaoLam", nv.NgayVaoLam);
                par[9] = new SqlParameter("@admin", false);

                String cmdText = "INSERT INTO NHAN_VIEN VALUES (@TenNhanVien, @CMND, @DiaChi, " +
                                 "@DienThoai, @Email, @MatKhau, @QuanLy, @KhongHoatDong, @NgayVaoLam, @admin)";

                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, par);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static void SuaNhanVien(Nhan_Vien nv)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@CMND", nv.CMND);
                par[1] = new SqlParameter("@DiaChi", nv.DiaChi);
                par[2] = new SqlParameter("@DienThoai", nv.DienThoai);
                par[3] = new SqlParameter("@Email", nv.Email);
                par[4] = new SqlParameter("@KhongHoatDong", nv.KhongHoatDong);
                par[5] = new SqlParameter("@QuanLy", nv.QuanLy);
                par[6] = new SqlParameter("@TenNhanVien", nv.TenNhanVien);

                String cmdText = "UPDATE NHAN_VIEN SET TenNV = @TenNhanVien, DiaChi = @DiaChi, " +
                                 "SoDienThoai = @DienThoai, Email = @Email, KhongHoatDong = @KhongHoatDong, " +
                                 "QuanLy = @QuanLy WHERE CMND = @CMND";

                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, par);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static DataTable LayDanhSachNhanVien()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@admin", false);
                String cmdText = "SELECT * FROM NHAN_VIEN WHERE Admin=@admin";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, par).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static String LayTenNhanVien(String manv)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@manv", manv);

                String cmdText = "SELECT TenNV FROM NHAN_VIEN WHERE CMND = @manv";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, par).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return rs.Rows[0][0].ToString();
        }

        public static void XoaNhanVien(String cmnd)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@CMND", cmnd);

                String cmdText = "DELETE NHAN_VIEN WHERE CMND = @CMND";

                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, par);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static Boolean DoiMatKhau(String cmnd, String mkcu, String mkmoi)
        {
            SqlConnection connect = null;
            Boolean flag = false;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[3];
                par[0] = new SqlParameter("@cmnd", cmnd);
                par[1] = new SqlParameter("@mkcu", MD5(mkcu));
                par[2] = new SqlParameter("@mkmoi", MD5(mkmoi));
                String cmdText = "UPDATE NHAN_VIEN SET MatKhau = @mkmoi WHERE CMND = @cmnd and MatKhau = @mkcu";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, par);
                flag = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
            return flag;
        }

        public static void ResetPassword(String manv)
        {
            SqlConnection connect = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@CMND", manv);
                par[1] = new SqlParameter("@matkhau", MD5(manv));
                String cmdText = "UPDATE NHAN_VIEN SET MatKhau = @matkhau WHERE CMND = @CMND";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, par);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }
    }
}
